﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using OSISStaffBusinessLayer;
using OpenSISDataConnection;
using System.Globalization;

/// <summary>
/// Summary description for StaffAddDL
/// </summary>
/// 
namespace OSISStaffDataLayer
{

    public class StaffDL
    {
        //public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];
        public static string server = "MSSQLServer";
        public StaffDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public DataSet PopulateEthnicity()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from ethnicity");
            return ds;
        }
        public DataSet PopulateLanguage()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from language");
            return ds;
        }
        public DataSet PopulateState()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("SELECT * FROM look_up_data WHERE TYPE=2");
            return ds;
        }
        public DataSet PopulateStaffType()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from staff_type");
            return ds;
        }
        public DataSet PopulateSchools()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from schools");
            return ds;
        }
        public DataSet populategender()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from gender");
            return ds;
        }

        public DataSet PopulatePhoneType()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from phone_type");
            return ds;
        }
        public DataSet PopulateAccessRole()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from access_roles");
            return ds;
        }

        public int InsertStaffDetails(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                object staffid;

                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax stf_id,staff");

                string insertstaff = "insert into staff (stf_id,language_id,gender_id,eth_id,stf_first_nm,stf_middle_nm,stf_last_nm,stf_dob,last_updated,stf_staff_id,stf_alternate_id,stf_salutation,stf_suffix,stf_secondry_lang_id,stf_third_lang_id,stf_disability,stf_disability_desc) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + Convert.ToInt32(STABL.Language) + "," + STABL.Gender + "," + STABL.Ethnicity + ",N'" + STABL.FirstName.Replace("'", "''") + "',N'" + STABL.MiddleName.Replace("'", "''") + "',N'" + STABL.LastName.Replace("'", "''") + "','" + STABL.DOB.Replace("'", "''") + "','" + STABL.LastUpdated + "',N'" + STABL.StaffIdNumber.Replace("'", "''") + "',N'" + STABL.Alternateid.Replace("'", "''") + "','" + STABL.Salutation + "','" + STABL.Suffix + "'," + STABL.Secondlang + "," + STABL.Thirdlang + ",'" + STABL.Disability + "',N'" + STABL.Disabilitydesc.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaff);

                staffid = DataConnection.ConnectionPath.Getdata("SELECT max(stf_id) from staff").Tables[0].Rows[0][0];
                return Convert.ToInt32(staffid);
            }
            if (server == "MySQL")
            {
                object staffid;

                DataSet ds = null;


                DateTime s = Convert.ToDateTime(STABL.DOB, DateTimeFormatInfo.InvariantInfo);
                STABL.DOB = s.ToString("yyyy/MM/dd");

                STABL.LastUpdated = "1900/01/01";


                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('stf_id','staff')");

                string insertstaff = "insert into staff (stf_id,language_id,gender_id,eth_id,stf_first_nm,stf_middle_nm,stf_last_nm,stf_dob,last_updated,stf_staff_id,stf_alternate_id,stf_salutation,stf_suffix,stf_secondry_lang_id,stf_third_lang_id,stf_disability,stf_disability_desc) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + Convert.ToInt32(STABL.Language) + "," + STABL.Gender + "," + STABL.Ethnicity + ",N'" + STABL.FirstName.Replace("'", "''") + "',N'" + STABL.MiddleName.Replace("'", "''") + "',N'" + STABL.LastName.Replace("'", "''") + "','" + STABL.DOB.Replace("'", "''") + "','" + STABL.LastUpdated + "',N'" + STABL.StaffIdNumber.Replace("'", "''") + "',N'" + STABL.Alternateid.Replace("'", "''") + "','" + STABL.Salutation + "','" + STABL.Suffix + "'," + STABL.Secondlang + "," + STABL.Thirdlang + ",'" + STABL.Disability + "',N'" + STABL.Disabilitydesc.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaff);

                staffid = DataConnection.ConnectionPath.Getdata("SELECT max(stf_id) from staff").Tables[0].Rows[0][0];
                return Convert.ToInt32(staffid);
            }
            else
            {
                int id = 0;
                return id;
            
            }


         
        }
        public void InsertStaffAccessDetails(StaffBL STABL)
        {

            if (server == "MSSQLServer")
            {

                string insertstaffaccess = "insert into staff_access (stf_id,stf_user_nm,stf_pwd,last_updated,stf_secret_ques,stf_secret_ans,stf_opensis_access,school_access) Values (" + STABL.StaffId + ",'" + STABL.Username.Replace("'", "''") + "','" + STABL.Password.Replace("'", "''") + "','" + STABL.LastUpdated + "','" + STABL.SecretQuestn.Replace("'", "''") + "','" + STABL.SecretAnswr.Replace("'", "''") + "','" + STABL.OpensisAcces + "','" + STABL.SchoolAccess + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffaccess);

            }
            if (server == "MySQL")
            {
                STABL.LastUpdated = "1900/01/01";

                string insertstaffaccess = "insert into staff_access (stf_id,stf_user_nm,stf_pwd,last_updated,stf_secret_ques,stf_secret_ans,stf_opensis_access,school_access) Values (" + STABL.StaffId + ",'" + STABL.Username.Replace("'", "''") + "','" + STABL.Password.Replace("'", "''") + "','" + STABL.LastUpdated + "','" + STABL.SecretQuestn.Replace("'", "''") + "','" + STABL.SecretAnswr.Replace("'", "''") + "','" + STABL.OpensisAcces + "','" + STABL.SchoolAccess + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffaccess);
            }

        }

        public void InsertStaffAddressDetails(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax stf_addr_id,staff_address");

                string insertstaffaddress = "insert into staff_address (stf_addr_id,stf_id,stf_addr1_pri,stf_addr2_pri,stf_city_pri,stf_st_pri,stf_zip_pri,stf_addr1_mail,stf_addr2_mail,stf_city_mail,stf_st_mail,stf_zip_mail,last_updated,stf_pobox_mail) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + ",'" + STABL.PrimaryAddress1.Replace("'", "''") + "','" + STABL.PrimaryAddress2.Replace("'", "''") + "','" + STABL.PrimaryCity.Replace("'", "''") + "'," + STABL.PrimaryState + ",'" + STABL.PrimaryZip.Replace("'", "''") + "','" + STABL.SecondaryAddress1.Replace("'", "''") + "','" + STABL.SecondaryAddress2.Replace("'", "''") + "','" + STABL.SecondaryCity.Replace("'", "''") + "'," + STABL.SecondaryState + ",'" + STABL.SecondaryZip + "','" + STABL.LastUpdated + "',N'" + STABL.PoBox.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffaddress);
            }
            if (server == "MySQL")
            {
                STABL.LastUpdated = "1900/01/01";

                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('stf_addr_id','staff_address')");

                string insertstaffaddress = "insert into staff_address (stf_addr_id,stf_id,stf_addr1_pri,stf_addr2_pri,stf_city_pri,stf_st_pri,stf_zip_pri,stf_addr1_mail,stf_addr2_mail,stf_city_mail,stf_st_mail,stf_zip_mail,last_updated,stf_pobox_mail) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + ",'" + STABL.PrimaryAddress1.Replace("'", "''") + "','" + STABL.PrimaryAddress2.Replace("'", "''") + "','" + STABL.PrimaryCity.Replace("'", "''") + "'," + STABL.PrimaryState + ",'" + STABL.PrimaryZip.Replace("'", "''") + "','" + STABL.SecondaryAddress1.Replace("'", "''") + "','" + STABL.SecondaryAddress2.Replace("'", "''") + "','" + STABL.SecondaryCity.Replace("'", "''") + "'," + STABL.SecondaryState + ",'" + STABL.SecondaryZip + "','" + STABL.LastUpdated + "',N'" + STABL.PoBox.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffaddress);
            }
            

        }
        public void InsertStaffPhoneDetails(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax stf_phn_id,staff_phone");

                string insertstaffphone = "insert into staff_phone (stf_phn_id,stf_id,last_updated,stf_home_phn,stf_mobile_phn,stf_offc_phn,stf_wrk_email,stf_personal_email) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + ",'" + STABL.LastUpdated + "','" + STABL.HomePhone.Replace("'", "''") + "','" + STABL.MobilPhon.Replace("'", "''") + "','" + STABL.Officphon.Replace("'", "''") + "',N'" + STABL.WorkEmail.Replace("'", "''") + "',N'" + STABL.Personalemail.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffphone);
            }
            if (server == "MySQL")
            {
                STABL.LastUpdated = "1900/01/01";


                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('stf_phn_id','staff_phone')");

                string insertstaffphone = "insert into staff_phone (stf_phn_id,stf_id,last_updated,stf_home_phn,stf_mobile_phn,stf_offc_phn,stf_wrk_email,stf_personal_email) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + ",'" + STABL.LastUpdated + "','" + STABL.HomePhone.Replace("'", "''") + "','" + STABL.MobilPhon.Replace("'", "''") + "','" + STABL.Officphon.Replace("'", "''") + "',N'" + STABL.WorkEmail.Replace("'", "''") + "',N'" + STABL.Personalemail.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffphone);
            }
            
            
        }

        public void InsertStaffSchoolJoinDetails(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax stf_join_schl_id,staff_join_school");

                string insertstaffschooljoin = "insert into staff_join_school (stf_join_schl_id,sch_id,stf_id,stf_hire_dt,stf_term_dt,last_updated,stf_job_title,stf_type_id) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.HomeSchoolId + "," + STABL.StaffId + ",'" + STABL.JoiningDate + "','" + STABL.LeftDate + "','" + STABL.LastUpdated + "',N'" + STABL.Jobtitle.Replace("'", "''") + "','" + STABL.StaffType + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffschooljoin);
            }
            if (server == "MySQL")
            {
                STABL.LastUpdated = "1900/01/01";

                DataSet ds = null;

                DateTime s = Convert.ToDateTime(STABL.JoiningDate, DateTimeFormatInfo.InvariantInfo);
                STABL.JoiningDate = s.ToString("yyyy/MM/dd");

                DateTime d = Convert.ToDateTime(STABL.LeftDate, DateTimeFormatInfo.InvariantInfo);
                STABL.LeftDate = d.ToString("yyyy/MM/dd");

                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('stf_join_schl_id','staff_join_school')");

                string insertstaffschooljoin = "insert into staff_join_school (stf_join_schl_id,sch_id,stf_id,stf_hire_dt,stf_term_dt,last_updated,stf_job_title,stf_type_id) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.HomeSchoolId + "," + STABL.StaffId + ",'" + STABL.JoiningDate + "','" + STABL.LeftDate + "','" + STABL.LastUpdated + "',N'" + STABL.Jobtitle.Replace("'", "''") + "','" + STABL.StaffType + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffschooljoin);

            }
         
        }

        public void InsertStaffCertificateDetails(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax stf_cert_id,staff_certification");

                string insertstaffcertdetails = "insert into staff_certification (stf_cert_id,stf_id,stf_cert_dt,stf_cert_exp_dt,stf_cert_cd,stf_cert_short_nm,stf_cert_nm,stf_pri_cert_ind,last_updated,stf_cert_desc) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + ",N'" + STABL.CertDate + "',N'" + STABL.CertExpirDate + "',N'" + STABL.CertCode.Replace("'", "''") + "',N'" + STABL.CertShortName.Replace("'", "''") + "',N'" + STABL.CertName.Replace("'", "''") + "',N'" + STABL.Primarycert + "',N'" + STABL.LastUpdated + "',N'" + STABL.CertDescription.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffcertdetails);

            }
            if (server == "MySQL")
            {
                STABL.LastUpdated = "1900/01/01";

                DataSet ds = null;

                DateTime d = Convert.ToDateTime(STABL.CertDate, DateTimeFormatInfo.InvariantInfo);
                STABL.CertDate = d.ToString("yyyy/MM/dd");

                DateTime s = Convert.ToDateTime(STABL.CertExpirDate, DateTimeFormatInfo.InvariantInfo);
                STABL.CertExpirDate = s.ToString("yyyy/MM/dd");

                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('stf_cert_id','staff_certification')");

                string insertstaffcertdetails = "insert into staff_certification (stf_cert_id,stf_id,stf_cert_dt,stf_cert_exp_dt,stf_cert_cd,stf_cert_short_nm,stf_cert_nm,stf_pri_cert_ind,last_updated,stf_cert_desc) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + ",N'" + STABL.CertDate + "',N'" + STABL.CertExpirDate + "',N'" + STABL.CertCode.Replace("'", "''") + "',N'" + STABL.CertShortName.Replace("'", "''") + "',N'" + STABL.CertName.Replace("'", "''") + "',N'" + STABL.Primarycert + "',N'" + STABL.LastUpdated + "',N'" + STABL.CertDescription.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffcertdetails);

            }
        }

        //Added newly 
        public void InsertStaffLibraryDetails(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax stf_lib_acces_id,staff_library_access");

                string insertstafflibrarydetails = "insert into staff_library_access (stf_lib_acces_id,stf_id,stf_lib_card_no,stf_lib_class,stf_status,stf_lib_access) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + ",'" + STABL.LibraryCard + "','" + STABL.LibraryClass + "','" + STABL.Status + "',N'" + STABL.LibraryAccess + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstafflibrarydetails);
            }
            if (server == "MySQL")
            {
                STABL.LastUpdated = "1900/01/01";

                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('stf_lib_acces_id','staff_library_access')");

                string insertstafflibrarydetails = "insert into staff_library_access (stf_lib_acces_id,stf_id,stf_lib_card_no,stf_lib_class,stf_status,stf_lib_access) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + ",'" + STABL.LibraryCard + "','" + STABL.LibraryClass + "','" + STABL.Status + "',N'" + STABL.LibraryAccess + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstafflibrarydetails);
            }

        }
        public void InsertStaffEmergncyContact(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax stf_emrgn_cont_id,staff_emergency_contact");

                string insertstaffemrgncontact = "insert into staff_emergency_contact (stf_emrgn_cont_id,stf_id,stf_emrgn_first_name,stf_emrgn_last_name,stf_emrgn_relationship,stf_emrgn_home_phn,stf_emrgn_mobile_phn,stf_emrgn_offc_phn,stf_emrgn_email) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + ",N'" + STABL.Emrgnfirstnam.Replace("'", "''") + "',N'" + STABL.Emrgnlastnam.Replace("'", "''") + "',N'" + STABL.Emrgnrelationship + "',N'" + STABL.EmrgnHomePhon.Replace("'", "''") + "',N'" + STABL.EmrgnMobilePhon.Replace("'", "''") + "',N'" + STABL.EmrgnWorkPhon.Replace("'", "''") + "',N'" + STABL.Emrgnemail.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffemrgncontact);
            }
            if (server == "MySQL")
            {

                STABL.LastUpdated = "1900/01/01";

                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('stf_emrgn_cont_id','staff_emergency_contact')");

                string insertstaffemrgncontact = "insert into staff_emergency_contact (stf_emrgn_cont_id,stf_id,stf_emrgn_first_name,stf_emrgn_last_name,stf_emrgn_relationship,stf_emrgn_home_phn,stf_emrgn_mobile_phn,stf_emrgn_offc_phn,stf_emrgn_email) Values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + ",N'" + STABL.Emrgnfirstnam.Replace("'", "''") + "',N'" + STABL.Emrgnlastnam.Replace("'", "''") + "',N'" + STABL.Emrgnrelationship + "',N'" + STABL.EmrgnHomePhon.Replace("'", "''") + "',N'" + STABL.EmrgnMobilePhon.Replace("'", "''") + "',N'" + STABL.EmrgnWorkPhon.Replace("'", "''") + "',N'" + STABL.Emrgnemail.Replace("'", "''") + "')";

                DataConnection.ConnectionPath.ExecuteCommand(insertstaffemrgncontact);
            }
        }


        public DataTable PopulateStaffBySchoolid(StaffBL STABL)
        {

           DataTable dt = null;

           string fetchstaff = "select staff.stf_id as staffid,stf_staff_id as staffidnumber,staff.stf_first_nm as firstname,staff.stf_last_nm as lastname,staff.stf_alternate_id as alternateid,staff.stf_disability as disability,staff.stf_disability_desc as disdescription,staff.stf_dob as DOB,staff.stf_middle_nm as middlename from staff left outer join staff_join_school SJS on staff.stf_id = SJS.stf_id left outer join staff_access STA on staff.stf_id = STA.stf_id where SJS.sch_id =" + STABL.SchoolId + " and stf_first_nm like '" + STABL.FirstName + "%' and stf_last_nm like '" + STABL.LastName + "%' and STA.stf_user_nm like '" + STABL.Username + "%'";



           dt = DataConnection.ConnectionPath.Getdata(fetchstaff).Tables[0];

            return dt;
        }


        public DataTable PopulateStaffdetails(StaffBL STABL)
        {

            DataTable dt = null;

            string fetchstaff = "select staff.stf_id as staffid,SCHL.sch_full_nm as schoolname,ETN.eth_nm as ethnicity, STTP.stf_type_name as stafftype,LANG.language_nm as firstlanguage,SecLANG.language_nm as secondlang,THRDLANG.language_nm as thirdlang, GEND.gender_nm as gender ,LUD.NAME as primarystate,LUDMail.NAME as mailstate,STEC.stf_emrgn_cont_id as emrgcontid,STEC.stf_emrgn_email as emrgnemail,STEC.stf_emrgn_first_name as emrgnfname,STEC.stf_emrgn_home_phn as emrgnhomeph,STEC.*, SJS.*,STA.*,STLA.* ,STPH.*,STAD.* ,stf_staff_id as staffidnumber,AR.acc_role_nm as profile, staff.* from staff inner join staff_join_school SJS on staff.stf_id = SJS.stf_id inner join staff_access STA on staff.stf_id = STA.stf_id left outer join access_roles AR on AR.acc_role_cd  = STA.acc_role_id left outer join staff_library_access STLA on staff.stf_id =STLA.stf_id inner join staff_phone STPH on staff.stf_id = STPH.stf_id inner join staff_emergency_contact STEC on staff.stf_id = STEC.stf_id inner join staff_address STAD on staff.stf_id = STAD.stf_id inner join language LANG on staff.language_id = LANG.language_id left outer join language SecLANG on staff.stf_secondry_lang_id = SecLANG.language_id left outer join language THRDLANG on staff.stf_third_lang_id = THRDLANG.language_id inner join gender GEND on staff.gender_id = GEND.gender_id inner join look_up_data LUD on  STAD.stf_st_pri = LUD.IID and LUD.TYPE = 2 left outer join look_up_data LUDMail on STAD.stf_st_mail = LUDMail.IID and LUDMail.TYPE = 2 left outer join ethnicity as ETN on staff.eth_id = ETN.eth_id inner join staff_type as STTP on SJS.stf_type_id = STTP.stf_type_id inner join schools SCHL on SJS.sch_id = SCHL.sch_id  where SJS.sch_id = " + STABL.SchoolId + " and staff.stf_id = " + STABL.StaffId + "";



            dt = DataConnection.ConnectionPath.Getdata(fetchstaff).Tables[0];

            return dt;
        }

        public DataTable PopulateSchoolaccesschkbox(StaffBL STABL)
        {

            DataTable dt = null;

            string fetchstaff = "select * from schools where sch_id in("+STABL.SchoolAccess+")";



            dt = DataConnection.ConnectionPath.Getdata(fetchstaff).Tables[0];

            return dt;
        }
        public DataTable PopulateCertification(StaffBL STABL)
        {

            DataTable dt = null;

            string fetchstaff = "select * from staff_certification where stf_id = "+STABL.StaffId+"";



            dt = DataConnection.ConnectionPath.Getdata(fetchstaff).Tables[0];

            return dt;
        }

        public System.Data.DataTable GetNextStaffId(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {

                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select top 1 stf_id  from staff_join_school where sch_id=" + STABL.SchoolId + " and stf_id>" + STABL.StaffId + "").Tables[0];
                return dt;
            }
            if (server == "MySQL")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select  stf_id  from staff_join_school where sch_id=" + STABL.SchoolId + " and stf_id>" + STABL.StaffId + " limit 1 ").Tables[0];
                return dt;
            }
            else
            {
                DataTable dt = null;

                return dt;
            }
        }

        public System.Data.DataSet GetPreviousStaffId(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                DataSet dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select top 1 stf_id  from staff_join_school where sch_id=" + STABL.SchoolId + " and stf_id<" + STABL.StaffId + " order by stf_id desc");
                return dt;
            }
            if (server == "MySQL")
            {
                DataSet dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select stf_id from staff_join_school where sch_id=" + STABL.SchoolId + " and stf_id<" + STABL.StaffId + " order by stf_id desc limit 1");
                return dt;
            }
            else
            {
                DataSet dt = null;

                return dt;
            }
        }

        public System.Data.DataTable GetFirstStaffIdBySchoolId(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select top 1 stf_id  from staff_join_school where sch_id=" + STABL.SchoolId + "").Tables[0];
                return dt;
            }
            if (server == "MySQL")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select stf_id  from staff_join_school where sch_id=" + STABL.SchoolId + " limit 1").Tables[0];
                return dt;
            }
            else
            {
                DataTable dt = null;

                return dt;
            }
        }

        public System.Data.DataTable GetLastStaffIdBySchoolId(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select top 1 stf_id  from staff_join_school where sch_id=" + STABL.SchoolId + " order by stf_id desc").Tables[0];
                return dt;
            }
            if (server == "MySQL")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("select stf_id  from staff_join_school where sch_id=" + STABL.SchoolId + " order by stf_id desc limit 1").Tables[0];
                return dt;
            }
            else
            {
                DataTable dt = null;

                return dt;
            }
        }



        public DataTable PopulateStaffdetailsEdit(StaffBL STABL)
        {

            DataTable dt = null;

            string fetchstaff = "select staff.stf_id as staffid,SCHL.sch_full_nm as schoolname,STEC.*, SJS.*,STA.*,STLA.* ,STPH.*,STAD.* ,stf_staff_id as staffidnumber,staff.* from staff inner join staff_join_school SJS on staff.stf_id = SJS.stf_id inner join staff_access STA on staff.stf_id = STA.stf_id left outer join staff_library_access STLA on staff.stf_id =STLA.stf_id inner join staff_phone STPH on staff.stf_id = STPH.stf_id inner join staff_emergency_contact STEC on staff.stf_id = STEC.stf_id inner join staff_address STAD on staff.stf_id = STAD.stf_id  inner join schools SCHL on SJS.sch_id = SCHL.sch_id  where SJS.sch_id = " + STABL.SchoolId + " and staff.stf_id = " + STABL.StaffId + "";



            dt = DataConnection.ConnectionPath.Getdata(fetchstaff).Tables[0];

            return dt;
        }




        public int CheckLibAccessbyStaffid(StaffBL STABL)
        {
            DataSet ds = null;
            int libaccess;

            string checklibaccess = "select count(*) from staff_library_access where stf_id ="+STABL.StaffId+"";

            ds = DataConnection.ConnectionPath.Getdata(checklibaccess);

            libaccess = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            return libaccess;



        }

        public void UpdateStaffDetails(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
               

                string updatestaff = "Update staff set  language_id ='" + STABL.Language + "',gender_id='" + STABL.Gender + "',eth_id='" + STABL.Ethnicity + "',stf_first_nm = N'" + STABL.FirstName + "',stf_middle_nm = N'" + STABL.MiddleName + "',stf_last_nm='" + STABL.LastName + "',stf_dob = N'" + STABL.DOB + "',last_updated='" + STABL.LastUpdated + "',stf_staff_id = N'" + STABL.StaffIdNumber + "',stf_alternate_id = N'" + STABL.Alternateid + "',stf_salutation = '" + STABL.Salutation + "',stf_suffix ='" + STABL.Suffix + "',stf_secondry_lang_id ='" + STABL.Secondlang + "',stf_third_lang_id ='" + STABL.Thirdlang + "',stf_disability ='" + STABL.Disability + "',stf_disability_desc = N'" + STABL.Disabilitydesc + "' where stf_id =" + STABL.StaffId + "";

                DataConnection.ConnectionPath.ExecuteCommand(updatestaff);
            }
            if (server == "MySQL")
            {
                DateTime s = Convert.ToDateTime(STABL.DOB, DateTimeFormatInfo.InvariantInfo);
                STABL.DOB = s.ToString("yyyy/MM/dd");
                STABL.LastUpdated = "1900/01/01";

                string updatestaff = "Update staff set  language_id ='" + STABL.Language + "',gender_id='" + STABL.Gender + "',eth_id='" + STABL.Ethnicity + "',stf_first_nm = N'" + STABL.FirstName + "',stf_middle_nm = N'" + STABL.MiddleName + "',stf_last_nm='" + STABL.LastName + "',stf_dob = N'" + STABL.DOB + "',last_updated='" + STABL.LastUpdated + "',stf_staff_id = N'" + STABL.StaffIdNumber + "',stf_alternate_id = N'" + STABL.Alternateid + "',stf_salutation = '" + STABL.Salutation + "',stf_suffix ='" + STABL.Suffix + "',stf_secondry_lang_id ='" + STABL.Secondlang + "',stf_third_lang_id ='" + STABL.Thirdlang + "',stf_disability ='" + STABL.Disability + "',stf_disability_desc = N'" + STABL.Disabilitydesc + "' where stf_id =" + STABL.StaffId + "";

                DataConnection.ConnectionPath.ExecuteCommand(updatestaff);
            }

            


        }

        public void UpdateStaffaddressDetails(StaffBL STABL)
        {


            string updateaddres = "Update staff_address set stf_addr1_pri = N'" + STABL.PrimaryAddress1 + "',stf_addr2_pri= N'" + STABL.PrimaryAddress2 + "',stf_city_pri= N'" + STABL.PrimaryCity + "',stf_st_pri = N'" + STABL.PrimaryState + "',stf_zip_pri = N'" + STABL.PrimaryZip + "',stf_addr1_mail= N'" + STABL.SecondaryAddress1 + "',stf_addr2_mail = N'" + STABL.SecondaryAddress2 + "',stf_city_mail= N'" + STABL.SecondaryCity + "',stf_st_mail ='" + STABL.SecondaryState + "',stf_zip_mail = N'" + STABL.SecondaryZip + "',stf_pobox_mail = N'" + STABL.PoBox + "' where stf_id =" + STABL.StaffId + "";

            DataConnection.ConnectionPath.ExecuteCommand(updateaddres);

      

        }

        public void UpdateStaffaccessDetails(StaffBL STABL)
        {


            string updateaccess = "Update staff_access set stf_user_nm = N'" + STABL.Username + "',stf_pwd= N'" + STABL.Password + "',stf_secret_ques = N'" + STABL.SecretQuestn + "',stf_secret_ans = N'" + STABL.SecretAnswr + "',stf_opensis_access ='" + STABL.OpensisAcces + "',school_access='" + STABL.SchoolAccess + "' where stf_id =" + STABL.StaffId + "";




            DataConnection.ConnectionPath.ExecuteCommand(updateaccess);




        }

        public void UpdateStaffphoneDetails(StaffBL STABL)
        {


            string updatephone = "Update staff_phone set stf_home_phn = N'" + STABL.HomePhone + "',stf_mobile_phn= N'" + STABL.MobilPhon + "',stf_offc_phn= N'" + STABL.Officphon + "',stf_wrk_email = N'" + STABL.WorkEmail + "',stf_personal_email = N'" + STABL.Personalemail + "' where stf_id =" + STABL.StaffId + "";

            DataConnection.ConnectionPath.ExecuteCommand(updatephone);

          


        }

        public void UpdateStaffschooljoinDetails(StaffBL STABL)
        {
            DateTime s = Convert.ToDateTime(STABL.JoiningDate, DateTimeFormatInfo.InvariantInfo);
            STABL.JoiningDate = s.ToString("yyyy/MM/dd");

            DateTime d = Convert.ToDateTime(STABL.LeftDate, DateTimeFormatInfo.InvariantInfo);
            STABL.LeftDate = d.ToString("yyyy/MM/dd");

            string updatescholljoin = "Update staff_join_school set sch_id ='" + STABL.HomeSchoolId + "',stf_hire_dt='" + STABL.JoiningDate + "',stf_term_dt='" + STABL.LeftDate + "',stf_job_title = N'" + STABL.Jobtitle + "',stf_type_id ='" + STABL.StaffType + "' where stf_id =" + STABL.StaffId + "";

            DataConnection.ConnectionPath.ExecuteCommand(updatescholljoin);


             
        }

        public void UpdateStaffemergencyDetails(StaffBL STABL)
        {


            string updateemergency= "Update staff_emergency_contact set stf_emrgn_first_name = N'" + STABL.Emrgnfirstnam + "',stf_emrgn_last_name= N'" + STABL.Emrgnlastnam + "',stf_emrgn_relationship ='" + STABL.Emrgnrelationship + "',stf_emrgn_home_phn = N'" + STABL.EmrgnHomePhon + "',stf_emrgn_mobile_phn= N'" + STABL.EmrgnMobilePhon + "',stf_emrgn_offc_phn = N'" + STABL.EmrgnWorkPhon + "',stf_emrgn_email= N'" + STABL.Emrgnemail + "' where stf_id =" + STABL.StaffId + "";

            DataConnection.ConnectionPath.ExecuteCommand(updateemergency);


          
        }

        public void UpdateStafflibaccessDetails(StaffBL STABL)
        {


            string updatelibacces = "Update staff_library_access set stf_lib_card_no = N'" + STABL.LibraryCard + "',stf_lib_class='" + STABL.LibraryClass + "',stf_status='" + STABL.Status + "',stf_lib_access ='" + STABL.LibraryAccess + "' where stf_id =" + STABL.StaffId + "";

            DataConnection.ConnectionPath.ExecuteCommand(updatelibacces);

          


        }



        public void UpdateStaffcertificationDetails(StaffBL STABL)
        {


            string updatecert = "Update staff_certification set stf_cert_dt ='" + STABL.CertDate + "',stf_cert_exp_dt='" + STABL.CertExpirDate + "',stf_cert_cd= N'" + STABL.CertCode + "',stf_cert_short_nm = N'" + STABL.CertShortName + "',stf_cert_nm = N'" + STABL.CertName + "',stf_pri_cert_ind ='" + STABL.Primarycert + "',stf_cert_desc = N'" + STABL.CertDescription + "' where stf_id =" + STABL.StaffId + " and stf_cert_id = "+STABL.Certificateid+"";

            DataConnection.ConnectionPath.ExecuteCommand(updatecert);



           

        }


        public void savestaffphoto(StaffBL STABL)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                object SchoolId = "";
                ds = DataConnection.ConnectionPath.Getdata("exec getMax stf_pic_id,staff_picture");
                String strStudentAdd = "INSERT into staff_picture values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + "," + STABL.HomeSchoolId + ",'" + STABL.PhotoUrl + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);

            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                object SchoolId = "";
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('stf_pic_id','staff_picture')");
                String strStudentAdd = "INSERT into staff_picture values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + STABL.StaffId + "," + STABL.SchoolId + ",'" + STABL.PhotoUrl + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);

            }


        }

        public DataTable PopulatestaffPhoto(StaffBL STABL)
        {
           
                DataTable dt = null;
                object SchoolId = "";
                
                String strStaffpic = "Select * from staff_picture where stf_id ="+STABL.StaffId+" and sch_id = "+STABL.HomeSchoolId+"";

                dt = DataConnection.ConnectionPath.Getdata(strStaffpic).Tables[0];

                return dt;

        }

        public void Updatestaffphoto(StaffBL STABL)
        {
            
                DataSet ds = null;
                object SchoolId = "";

                String strupdatestaffphoto = "Update staff_picture set name = '" + STABL.PhotoUrl + "' where stf_id ="+STABL.StaffId+" and sch_id = "+STABL.HomeSchoolId+" ";

                DataConnection.ConnectionPath.ExecuteCommand(strupdatestaffphoto);

           
        }
    
    }
}
